﻿USE [TanNamChinh]
/*
Cập nhật CSDL ngày 12-08-2013
*/


/*================================
1. Trong tblHBL:
	+ Xóa quan hệ tblHBL với tblFile (xóa column ID_FILE trong tblHBL)
	+ Xóa column ID_Customer
*/
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblHBL_tblFile]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblHBL]'))
ALTER TABLE [dbo].[tblHBL] DROP CONSTRAINT [FK_tblHBL_tblFile]
GO
ALTER TABLE [dbo].[tblHBL]
	DROP ID_FILE,ID_Customer

GO
ALTER TABLE [dbo].[tblHBL]
	ADD [ID_MBL] [int]
GO	
ALTER TABLE [dbo].[tblHBL]  WITH CHECK ADD  CONSTRAINT [FK_tblHBL_tblMBL] FOREIGN KEY([ID_MBL])
REFERENCES [dbo].[tblMBL] ([ID_MBL])
GO
ALTER TABLE [dbo].[tblHBL] CHECK CONSTRAINT [FK_tblHBL_tblMBL]

/*================================	
2. tblFile: Sửa column Is_Export thành IsExport và di chuyển lên sát với ID_File
*/
GO
EXEC sp_rename 'dbo.tblFile.Is_Export', 'IsExport', 'COLUMN'

/*================================	
3. tblHBLCont: xóa column ID_MBL
*/
GO
ALTER TABLE [dbo].[tblHBLCont]
	DROP ID_MBL



/*================================	
4. tblMBL: Thêm note cho column BookingNo là "Số Booking với hãng tàu"
*/
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Số Booking với hãng tàu' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblMBL', @level2type=N'COLUMN',@level2name=N'BookingNo'

/*================================	
5. Xóa các column ở bảng tblFile
*/
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFile_tblVessel]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFile]'))
ALTER TABLE [dbo].[tblFile] DROP CONSTRAINT [FK_tblFile_tblVessel]
GO
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFile_tblPort]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFile]'))
ALTER TABLE [dbo].[tblFile] DROP CONSTRAINT [FK_tblFile_tblPort]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFile_tblPort1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFile]'))
ALTER TABLE [dbo].[tblFile] DROP CONSTRAINT [FK_tblFile_tblPort1]
GO
ALTER TABLE [dbo].[tblFile]
DROP COLUMN [ID_Vessel], [ID_POL], [ETD], [ETA], [ID_POD],[NotifyParty],[GoodDescription],[GrossWeight]




/*================================	
7. Thêm column ở bảng tblHBL	
*/
GO
ALTER TABLE [dbo].[tblHBL]
ADD [NotifyParty] [nvarchar](50),
	[GoodDescription] [nvarchar](100),
	[GrossWeight] [float]
 
/*================================	
8. Cập nhật lại bảng tblCustomer: Cập nhật lại giá trị mặc định của column isActive = 1 
*/
GO
ALTER TABLE [dbo].[tblCustomer] ADD  CONSTRAINT [DF_tblCustomer_isActive]  DEFAULT ((1)) FOR [isActive]
GO

/*================================	
9. Thêm bảng tblVoyage 
*/
CREATE TABLE [dbo].[tblVoyage](
	[ID_Voyage] [int] IDENTITY(1,1) NOT NULL,
	[VoyageNo] [nvarchar](50) NULL,
	[ID_Vessel] [int] NULL,
	[ID_POL] [int] NULL,
	[ID_POD] [int] NULL,
	[ETD] [smalldatetime] NULL,
	[ETA] [smalldatetime] NULL,
 CONSTRAINT [PK_tblVoyage] PRIMARY KEY CLUSTERED 
(
	[ID_Voyage] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chuyến tàu số' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVoyage', @level2type=N'COLUMN',@level2name=N'VoyageNo'
GO

ALTER TABLE [dbo].[tblVoyage]  WITH CHECK ADD  CONSTRAINT [FK_tblVoyage_tblPort] FOREIGN KEY([ID_POD])
REFERENCES [dbo].[tblPort] ([ID_Port])
GO

ALTER TABLE [dbo].[tblVoyage] CHECK CONSTRAINT [FK_tblVoyage_tblPort]
GO

ALTER TABLE [dbo].[tblVoyage]  WITH CHECK ADD  CONSTRAINT [FK_tblVoyage_tblPort1] FOREIGN KEY([ID_POL])
REFERENCES [dbo].[tblPort] ([ID_Port])
GO

ALTER TABLE [dbo].[tblVoyage] CHECK CONSTRAINT [FK_tblVoyage_tblPort1]
GO

ALTER TABLE [dbo].[tblVoyage]  WITH CHECK ADD  CONSTRAINT [FK_tblVoyage_tblVessel] FOREIGN KEY([ID_Vessel])
REFERENCES [dbo].[tblVessel] ([ID_Vessel])
GO

ALTER TABLE [dbo].[tblVoyage] CHECK CONSTRAINT [FK_tblVoyage_tblVessel]
GO

/*================================	
10. Thêm column ID_Voyage ở bảng tblMBL
*/
GO
ALTER TABLE [dbo].[tblMBL]
ADD [ID_Voyage] [int]
	
--Cập nhật ràng buộc khóa ngoại ID_Voyage
GO
ALTER TABLE [dbo].[tblMBL]  WITH CHECK ADD  CONSTRAINT [FK_tblMBL_tblVoyage] FOREIGN KEY([ID_Voyage])
REFERENCES [dbo].[tblVoyage] ([ID_Voyage])
GO
ALTER TABLE [dbo].[tblMBL] CHECK CONSTRAINT [FK_tblMBL_tblVoyage]
GO

